﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;
using Common;
using System.Reflection;
using System.IO;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace 包覆门.OrderManager
{
    /// <summary>
    /// 导出包覆门订单
    /// </summary>
    public class ExportOrderExcel
    {
        public string Danhao { get; set; }
        public bool IsDoton { get; set; }

        private OrderDAL dal = new OrderDAL();//数据仓库
        private AccountDAL acDal = new AccountDAL();
        private DictionaryTypeDAL dicDal = new DictionaryTypeDAL();
        DataTable mainorder = new DataTable();//主订单信息
        DataTable detailorder = new DataTable();//详细订单信息
        DataTable account = new DataTable();//打款账号
        DataTable other = new DataTable();//特殊工艺
        List<string> list = new List<string>();//特殊费用存储
        Dictionary<string, string> dicmain = new Dictionary<string, string>();//单独
        DataTable menxingorder = new DataTable();
        DataTable zsxtorder = new DataTable();
        //路径
        readonly string modelPath = AppDomain.CurrentDomain.BaseDirectory + CommonHelper.GetPath("orderpath");
        readonly string textPath = AppDomain.CurrentDomain.BaseDirectory + CommonHelper.GetPath("orderDescpath");
        //readonly string exportPath = AppDomain.CurrentDomain.BaseDirectory + "\\exportExcel\\导出订单" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        //excel
        readonly object miss = Missing.Value;
        readonly Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();//excel实例

        public void OutExcel()
        {
            //门型订单详情
             menxingorder = dal.ReportMXOrderByDanhao(Danhao);
            //装饰线条详情
            zsxtorder = dal.ReportZSXTOrderByDanhao(Danhao);
            //获取相应数据源
            GetMainOrder(Danhao);
            GetDetailOrder(Danhao);
            GenerateOther();
            GenerateDic();
            GetAccount();
            //导出位置
            string name = IsDoton ? "大唐订单" : "OEM订单";
            string exportPath = AppDomain.CurrentDomain.BaseDirectory + "\\exportExcel\\" + name + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

            //记录新增行数
            int index1 = 0;
            int index2 = 0;
            int index3 = 0;
            int index4 = 0;
            string strText = "";
            var readText = new StreamReader(textPath, Encoding.GetEncoding("GB2312"));
            var mode = new FileInfo(modelPath);
            try
            {
                mode.CopyTo(exportPath);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                myExcel.Workbooks.Close();
                myExcel.Quit();
            }
            myExcel.Visible = true;
            myExcel.Application.Workbooks.Open(exportPath, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            var mySheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Sheets[1];
           //进度条
            //WaitingForm wf = new WaitingForm();
            //wf.ShowDialog();
            //wf.StartWaiting();
            while (strText != null)
            {
                if (!strText.StartsWith("--") && strText != "")
                {
                    //string[] sArray = Regex.Split(strTxt, "::", RegexOptions.IgnoreCase);
                    //string[] XYAxis = sArray[3].Split(',');
                    string[] sArray = Regex.Split(strText, "::", RegexOptions.IgnoreCase);
                    string[] xyAxis = sArray[3].Split(',');
                    int xAxis = Convert.ToInt16(xyAxis[0]);
                    int yAxis = Convert.ToInt16(xyAxis[1]);
                    //int detail = 0;
                    //int wl = 0;
                    try
                    {
                        switch (sArray[1])
                        {
                            case "DICMAIN":
                                if (mainorder.Rows.Count <= 0)
                                    break;
                                mySheet.Cells[xAxis, yAxis] = dicmain[sArray[2]];
                                break;
                            case "MAINORDER":
                                if (menxingorder.Rows.Count <= 0)
                                    break;
                                for (int i = 0; i < menxingorder.Rows.Count; i++)
                                {
                                    if (xAxis + i > 8 && yAxis == 2)
                                    {
                                        InsertRow(9, index1, xAxis, mySheet);
                                        index1 = index1 + 1;
                                    }
                                    mySheet.Cells[xAxis + i, yAxis] = menxingorder.Rows[i][sArray[2]];
                                }
                                break;
                            case "ZSXTORDER":
                                if (zsxtorder.Rows.Count <= 0)
                                    break;
                                for (int i = 0; i < zsxtorder.Rows.Count; i++)
                                {
                                    if (xAxis + i > 12 && yAxis == 2)
                                    {
                                        InsertRowZsxt(13, index1+index2, xAxis + index1, mySheet);
                                        index2 = index2 + 1;
                                    }
                                    mySheet.Cells[xAxis + i+index1, yAxis] = zsxtorder.Rows[i][sArray[2]];
                                }
                                break;
                            case "SPECIALORDER":
                                if (other.Rows.Count <= 0)
                                    break;
                                for (int i = 0; i < other.Rows.Count; i++)
                                {
                                    if (xAxis + i > 16 && yAxis == 8)
                                    {
                                        InsertSpecialRow(17, index1 + index2+index3,xAxis+index1+index2 ,mySheet);
                                        index3 = index3 + 1;
                                    }
                                    mySheet.Cells[xAxis + i + index1+index2, yAxis] = other.Rows[i][sArray[2]];
                                }
                                break;
                            case "ACCOUNT":
                                if (account.Rows.Count <= 0)
                                {
                                    break;
                                }
                                for (int i = 0; i < account.Rows.Count; i++) 
                                {
                                    if (xAxis + i > 23 && yAxis == 1) 
                                    {
                                        InsertAccountRow(24, index1+index2+index3+index4, mySheet);
                                        index4 = index4 + 1;
                                    }
                                    mySheet.Cells[xAxis + i + index1 + index2+index3, yAxis] = account.Rows[i][sArray[2]];
                                }
                                break;

                        }
                    }
                    catch (Exception ex)
                    {

                        MessageBox.Show(@"数据读取错误!" + ex.Message);
                    }
                }

                strText = readText.ReadLine();
            }
            //wf.StopWaiting();
            //wf.Close();
            //myExcel.Visible = true;

        }


        //获取主单信息
        public void GetMainOrder(string danhao) {
            mainorder = dal.ReportMainOrderByDanhaoExcel(Danhao);
        }


        //查询详情订单信息
        public void GetDetailOrder(string danhao) {
            detailorder = dal.ReportDetailOrderByDanhao(Danhao);
            detailorder.Columns.Add(new DataColumn("mianji1", typeof(System.Decimal), "Convert(mianji,System.Decimal)"));
            detailorder.Columns.Add(new DataColumn("total1", typeof(System.Decimal), "Convert(total,System.Decimal)"));
            detailorder.Columns.Add(new DataColumn("num1", typeof(System.Decimal), "Convert(num,System.Decimal)"));
            detailorder.Columns.Add(new DataColumn("gao1", typeof(System.Decimal), "Convert(gao,System.Decimal)"));
            detailorder.Columns.Add(new DataColumn("glassprice1", typeof(System.Decimal), "Convert(glassprice,System.Decimal)"));
            detailorder.Columns.Add(new DataColumn("oldprice1", typeof(System.Decimal), "Convert(oldprice,System.Decimal)"));
            detailorder.Columns.Add(new DataColumn("otherprice1", typeof(System.Decimal), "Convert(otherprice,System.Decimal)"));

            detailorder.Columns.Add(new DataColumn("zscd", typeof(System.Decimal), "gao1*num1"));
            
        }
        
        //查询账号信息
        public void GetAccount() {
            if (IsDoton)
            {
                account = acDal.ListDotonAll();
            }
            else
            {
                account = acDal.ListOemAll();
            }
        }
        //特殊加工信息
        public void GenerateOther() {
            other.Columns.Add(new DataColumn("proname", typeof(string)));
            other.Columns.Add(new DataColumn("pronum", typeof(string)));
            other.Columns.Add(new DataColumn("proprice", typeof(string)));
            other.Columns.Add(new DataColumn("prototal", typeof(string)));
            other.Columns.Add(new DataColumn("prounit", typeof(string)));
            string menxingAdd = "";

            //查询做旧价格价格字典
            DataTable dtColor = dicDal.ColorListAll();

            //查询玻璃
            DataTable dtGlass = dicDal.SpecialGlassListAll();
            //芯板做旧填充内容
            DataRow dr1 = other.NewRow();
            dr1["proname"] = "芯板做旧";
            object o1 = SqlHelper.FromDbValue(detailorder.Compute("sum(mianji1)", "isdel='0' and zslb='0' and isold='1'"));

            dr1["pronum"] = (o1 == null ? "0" : o1.ToString());
            dr1["prounit"] = "㎡";
            DataTable distinctColor = dal.GetReportColorDistint(Danhao);
            if (distinctColor.Rows.Count > 1)
            {
                MessageBox.Show("芯板做旧颜色不能存在两种以上，请确认");
                return;
            }
            else if (distinctColor.Rows.Count == 1)
            {
                DataRow[] drsColor = dtColor.Select("mid='" + distinctColor.Rows[0]["oldcolorid"] + "'");
                dr1["proprice"] = drsColor[0]["mprice"].ToString();
                dr1["proname"] = drsColor[0]["mcolor"].ToString() + "芯板做旧";
                //specialData.Rows.Add(dr1);
            }
            else
            {
                dr1["proprice"] = "0";
            }
            string p1 = detailorder.Compute("sum(oldprice1)", "isdel='0' and zslb='0' and isold='1'").ToString();
            list.Add(p1);
            dr1["prototal"] = p1;
            other.Rows.Add(dr1);
            //玻璃附加费填充内容
            DataRow dr2 = other.NewRow();
            dr2["proname"] = "玻璃附加费";
            object o2 = detailorder.Compute("sum(mianji1)", "isdel='0' and zslb='0' and isglass='1'");
            dr2["pronum"] = (o2 == null ? "0" : o2.ToString());
            dr2["prounit"] = "㎡";
            DataTable distinctGlass = dal.GetReportGlassDistint(Danhao);
            if (distinctGlass.Rows.Count > 1)
            {
                MessageBox.Show("玻璃芯板不能存在两种以上，请确认");
                return;
            }
            else if (distinctGlass.Rows.Count == 1)
            {
                DataRow[] drsGlass = dtGlass.Select("gid='" + distinctGlass.Rows[0]["glassid"] + "'");
                dr2["proprice"] = drsGlass[0]["glassprice"].ToString();
                dr2["proname"] = drsGlass[0]["glasstype"].ToString() + "替换";
                // specialData.Rows.Add(dr1);
            }
            else
            {
                dr2["proprice"] = "0";
            }
            string p2 = detailorder.Compute("sum(glassprice1)", "isdel='0' and zslb='0' and isglass='1'").ToString();
            list.Add(p2);
            dr2["prototal"] = p2;
            other.Rows.Add(dr2);
            //装饰线条附加内容
            DataRow dr3 = other.NewRow();
            dr3["proname"] = "装饰线条做旧费";
            object zt = SqlHelper.FromDbValue(detailorder.Compute("sum(zscd)", "isdel='0' and zslb='1' and isold='1' and iszt='0'"));
            decimal zz = (zt == null ? 0 : Convert.ToDecimal(zt));
            decimal zscd = Math.Round(zz / 1000, 3);
            dr3["pronum"] = zscd;
            dr3["prounit"] = "m";
            DataTable distinctZsxt = dal.GetReportZsxtColorDistint(Danhao);
            if (distinctZsxt.Rows.Count > 1)
            {
                MessageBox.Show("装饰线条做旧颜色不能存在两种以上，请确认");
                return;
            }
            else if (distinctZsxt.Rows.Count == 1)
            {
                DataRow[] drZsxt = dtColor.Select("mid='" + distinctZsxt.Rows[0]["oldcolorid"].ToString() + "'");
                dr3["proprice"] = drZsxt[0]["mprice"].ToString();
                dr3["proname"] = drZsxt[0]["mcolor"].ToString()+"(柱身)";
            }
            else
            {
                dr3["proprice"] = "0";
            }
            string p3 = detailorder.Compute("sum(oldprice1)", "isdel='0' and zslb='1' and isold='1' and iszt='0'").ToString();
            list.Add(p3);
            dr3["prototal"] = p3;
            other.Rows.Add(dr3);
            //柱头计算
            //DataRow dr6 = other.NewRow();
            //dr6["proname"] = "柱头做旧费";

            //dr6["pronum"] = detailorder.Compute("sum(num1)", "isdel='0' and zslb='1' and isold='1' and iszt='1'").ToString();
            //dr6["prounit"] = "个";
            //DataTable distinctZtzj = dal.GetReportZtzjColorDistint(Danhao);
            //if (distinctZtzj.Rows.Count > 1)
            //{
            //    MessageBox.Show("装饰线条做旧颜色不能存在两种以上，请确认");
            //    return;
            //}
            //else if (distinctZtzj.Rows.Count == 1)
            //{
            //    DataRow[] drZsxt = dtColor.Select("mid='" + distinctZsxt.Rows[0]["oldcolorid"].ToString() + "'");
            //    //dr6["proprice"] = drZsxt[0]["mprice"].ToString();
            //    dr6["proname"] = drZsxt[0]["mcolor"].ToString()+"(柱头)";
            //}
            //else
            //{
            //    dr6["proprice"] = "0";
            //}
            //string p6 = detailorder.Compute("sum(oldprice1)", "isdel='0' and zslb='1' and isold='1' and iszt='1' ").ToString();
            //list.Add(p6);
            //dr6["prototal"] = p6;
            //other.Rows.Add(dr6);



            //门型附加费
            DataRow dd = null;
            DataRow dr6 = null;
            DataRow dr5 = null;
            DataRow[] drspackage = null;
            DataRow drpackage = null;
            foreach (DataRow d in detailorder.Rows)
            {
                
                if (d["isspecial"].ToString().Equals("1"))
                {
                    menxingAdd = menxingAdd + d["specialname"].ToString() + "、";
                    dd = other.NewRow();
                    dd["proname"] = d["specialname"];
                    dd["pronum"] = d["num"];
                    dd["prounit"] = "个";
                    string pp = d["specialtotal"].ToString();
                    list.Add(pp);
                    dd["proprice"] = d["specialprice"];
                    dd["prototal"] = pp;
                    other.Rows.Add(dd);
                }
                if (d["zslb"].ToString().Equals("1") && d["iszt"].ToString().Equals("1") && d["isold"].ToString().Equals("1")) 
                {
                    //柱头计算
                    dr6 = other.NewRow();
                    dr6["proname"] = "柱头做旧费";

                    //dr6["pronum"] = detailorder.Compute("sum(num1)", "isdel='0' and zslb='1' and isold='1' and iszt='1'").ToString();
                    dr6["pronum"] = detailorder.Compute("sum(zscd)/1000", "isdel=0 and zslb='1' and iszt='1' and isold='1'").ToString();
                    dr6["prounit"] = "米";
                    DataTable distinctZtzj = dal.GetReportZtzjColorDistint(Danhao);
                    if (distinctZtzj.Rows.Count > 1)
                    {
                        MessageBox.Show("装饰线条做旧颜色不能存在两种以上，请确认");
                        return;
                    }
                    else if (distinctZtzj.Rows.Count == 1)
                    {
                        DataRow[] drZsxt = dtColor.Select("mid='" + distinctZtzj.Rows[0]["oldcolorid"].ToString() + "'");
                        //dr6["proprice"] = dal.GetZtPrice(d["mxbh"].ToString());
                        dr6["proprice"] = dal.GetZsxtOldPrice(d["oldColorId"].ToString());
                        dr6["proname"] = drZsxt[0]["mcolor"].ToString() + "(柱头)";
                    }
                    else
                    {
                        dr6["proprice"] = "0";
                    }
                    string p6 = detailorder.Compute("sum(oldprice1)", "isdel='0' and zslb='1' and isold='1' and iszt='1' ").ToString();
                    list.Add(p6);
                    dr6["prototal"] = p6;
                    other.Rows.Add(dr6);
                }
                //其他费用
                if (Convert.ToDecimal(d["otherprice1"]) > 0)
                {
                    dr5 = other.NewRow();
                    dr5["proname"] = d["otherSm"];
                    dr5["pronum"] = "1";
                    dr5["prounit"] = "备注";
                    string p5 = d["otherprice1"].ToString();
                    list.Add(p5);
                    dr5["proprice"] = p5;
                    dr5["prototal"] = p5;
                    other.Rows.Add(dr5);
                }
            }
            foreach (DataRow d in detailorder.Rows)
            {
                //包装费用
                if (d["zslb"].ToString().Equals("0"))
                {
                    if (!string.IsNullOrWhiteSpace(d["packagetotal"].ToString()) && Convert.ToDecimal(d["packagetotal"]) > 0)
                    {
                        drspackage = other.Select("proname='" + d["packagename"].ToString() + "'");
                        if (drspackage != null && drspackage.Length > 0)
                        {

                            //drspackage[0]["proname"] = d["packagename"];
                            drspackage[0]["pronum"] = Convert.ToDecimal(d["mianji"]) + Convert.ToDecimal(drspackage[0]["pronum"]);
                            // drspackage[0]["prounit"] = "元/㎡";
                            string pp = d["packagetotal"].ToString();
                            list.Add(pp);
                            drspackage[0]["proprice"] = d["packageprice"];
                            drspackage[0]["prototal"] = Convert.ToDecimal(d["packagetotal"]) + Convert.ToDecimal(drspackage[0]["prototal"]);

                        }
                        else
                        {
                            drpackage = other.NewRow();
                            drpackage["proname"] = d["packagename"];
                            drpackage["pronum"] = d["mianji"];
                            drpackage["prounit"] = "元/㎡";
                            string pp = d["packagetotal"].ToString();
                            list.Add(pp);
                            drpackage["proprice"] = d["packageprice"];
                            drpackage["prototal"] = pp;
                            other.Rows.Add(drpackage);
                        }
                    }
                }
            }

            //包装费用
            //包装费用
            if (!string.IsNullOrWhiteSpace(mainorder.Rows[0]["bzfy"].ToString()) && Convert.ToDecimal(mainorder.Rows[0]["bzfy"].ToString()) > 0)
            {
                DataRow dr4 = other.NewRow();
                dr4["proname"] = "单独木箱包装(个)";
                dr4["prounit"] = "包";
                string p4 = mainorder.Rows[0]["bzfy"].ToString();
                list.Add(p4);
                DataTable dt = dicDal.GetPackageUnitPrice();
                if (dt.Rows.Count > 0)
                {
                    DataRow du = dt.Rows[0];
                    dr4["pronum"] = Math.Round(Convert.ToDecimal(p4) / Convert.ToDecimal(du["unitprice"]));
                    dr4["proprice"] = du["unitprice"];
                }
                dr4["prototal"] = p4;
                other.Rows.Add(dr4);
            }
            //拉手
            DataRow drmain = mainorder.Rows[0];
            if (!string.IsNullOrWhiteSpace(drmain["handerid"].ToString()))
            {
                DataRow dr7 = other.NewRow();
                dr7["proname"] = drmain["handername"];
                dr7["pronum"] = drmain["handernum"];
                dr7["prounit"] = "个";
                string p7 = drmain["handertotal"].ToString();
                list.Add(p7);
                dr7["proprice"] = drmain["handerprice"];
                dr7["prototal"] = p7;
                other.Rows.Add(dr7);
            }
            if (!string.IsNullOrWhiteSpace(drmain["hingeid"].ToString()))
            {
                DataRow dr8 = other.NewRow();
                dr8["proname"] = drmain["hingeDesc"];
                dr8["pronum"] = drmain["hingenum"];
                dr8["prounit"] = "个";
                string p8 = drmain["hingetotal"].ToString();
                list.Add(p8);
                dr8["proprice"] = drmain["hingeprice"];
                dr8["prototal"] = p8;
                other.Rows.Add(dr8);
            }
            ////其他费用
            //DataRow dr5 = other.NewRow();
            //dr5["proname"] = "其他费用";
            //dr5["pronum"] = "1";
            //dr5["prounit"] = "件";
            //string p5 = detailorder.Compute("sum(otherprice1)", "isdel='0' ").ToString();
            //list.Add(p5);
            //dr5["proprice"] = p5;
            //dr5["prototal"] = p5;
            //other.Rows.Add(dr5);
        }

        //单字段
        /// <summary>
        /// 1.客户::DICMAIN::SNAME::2,3
        //2.电话::DICMAIN::PHONE::3,3
        //3.订单编号::DICMAIN::DANHAO::2,11
        //4.制单日期::DICMAIN::ZDRQ::3,11
        //5.接单员::DICMAIN::USERNAME::4,11
        //6.合同单号::DICMAIN::PLANORDER::4,13
        //7.客户地址::DICMAIN::ADDRESS::4,3
        //8.总数量::DICMAIN::TOTALNUM::9,9
        //9.总面积::DICMAIN::TOTALMIANJI::9,11
        //10.金额::DICMAIN::TOTALPRICE::9,13
        //11.总金额::DICMAIN::ORDERPRICE::13,13
        /// </summary>
        public void GenerateDic() {
            if (mainorder.Rows.Count == 1)
            {
                string totalmianji = detailorder.Compute("sum(mianji1)", "isdel='0' and zslb='0' ").ToString();
                if (string.IsNullOrWhiteSpace(totalmianji)) totalmianji = "0";
                //string totalmianji = detail.Compute("sum('mianji')", "isdel='0' and zslb='0' ").ToString();
                string totalPrice = detailorder.Compute("sum(total1)", "isdel='0' and zslb='0'").ToString();
                string totalnum = detailorder.Compute("sum(num1)", "isdel=0 and zslb='0'").ToString();
                string zstotalPrice = detailorder.Compute("sum(total1)", "isdel='0' and zslb='1'").ToString();
                string zstotalnum = detailorder.Compute("sum(num1)", "isdel=0 and zslb='1'").ToString();
                string zstotalmeter = detailorder.Compute("sum(zscd)/1000", "isdel=0 and zslb='1' and iszt='0'").ToString();
                DataRow dr = mainorder.Rows[0];
                if (IsDoton)
                {
                    
                    dicmain.Add("REPORTNAME", "大唐宅配包覆平开门订单");
                }
                else
                {
                    dicmain.Add("REPORTNAME", "（外包）包覆平开门订单");
                }

                dicmain.Add("SNAME", dr["sname"].ToString());
                dicmain.Add("PHONE", dr["phone"].ToString());
                dicmain.Add("DANHAO", dr["danhao"].ToString());
                dicmain.Add("ZDRQ", DateTime.Now.ToString("yyyy-MM-dd"));
                dicmain.Add("USERNAME", dr["username"].ToString());
                dicmain.Add("PLANORDER", dr["planOrder"].ToString());
                dicmain.Add("ADDRESS", dr["address"].ToString());
                dicmain.Add("TOTALNUM", string.IsNullOrWhiteSpace(totalnum) ? "0" : totalnum);
                dicmain.Add("TOTALMIANJI", string.IsNullOrWhiteSpace(totalmianji) ? "0" : totalmianji);
                dicmain.Add("TOTALPRICE", string.IsNullOrWhiteSpace(totalPrice) ? "0" : totalPrice);
                dicmain.Add("ZSTOTALNUM", string.IsNullOrWhiteSpace(zstotalnum) ? "0" : zstotalnum);
                dicmain.Add("ZSTOTALPRICE", string.IsNullOrWhiteSpace(zstotalPrice) ? "0" : zstotalPrice);
                dicmain.Add("HINGENAME", dr["hingename"].ToString());
                dicmain.Add("ZSTOTALMETER", zstotalmeter);
                //计算总费用
                decimal op = 0;
                foreach (string s in list)
                {

                    op += Convert.ToDecimal((string.IsNullOrWhiteSpace(s) ? "0" : s));
                }
                string o = Math.Round(op + Convert.ToDecimal((string.IsNullOrWhiteSpace(totalPrice) ? "0" : totalPrice)) + Convert.ToDecimal((string.IsNullOrWhiteSpace(zstotalPrice) ? "0" : zstotalPrice)), 0).ToString();
                dicmain.Add("ORDERPRICE", o);
            }
            else
            {
                MessageBox.Show("出现两个订单，请联系管理员处理");
                return;

            }
           

           
        }
        /// <summary>
        /// 主单增加一空行
        /// </summary>
        /// <param name="BeginRowNumber"> 起始号码</param>
        /// <param name="InD">增加的行数</param>
        /// <param name="workSheet">工作表</param>
        public void InsertRow(int BeginRowNumber, int InD,int topInD, Microsoft.Office.Interop.Excel.Worksheet workSheet)
        {
            var range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[BeginRowNumber + InD, Type.Missing];
            range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 1], workSheet.Cells[BeginRowNumber + InD, 15]];
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            range = workSheet.Range[workSheet.Cells[topInD, 1], workSheet.Cells[BeginRowNumber + InD, 1]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            //range = workSheet.get_Range(workSheet.Cells[BeginRowNumber + InD, 3], workSheet.Cells[BeginRowNumber + InD, 44]);
            //range.Merge(Missing.Value);
            //range.Select();
            //RunMacro("BK");
        }
        /// <summary>
        /// 装饰线条增加一空行
        /// </summary>
        /// <param name="BeginRowNumber"> 起始号码</param>
        /// <param name="InD">增加的行数</param>
        /// <param name="workSheet">工作表</param>
        public void InsertSpecialRow(int BeginRowNumber, int InD, int topInD, Microsoft.Office.Interop.Excel.Worksheet workSheet)
        {
            var range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[BeginRowNumber + InD, Type.Missing];
            range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 1], workSheet.Cells[BeginRowNumber + InD, 15]];
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[topInD, 1], workSheet.Cells[BeginRowNumber + InD, 7]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 8], workSheet.Cells[BeginRowNumber + InD, 10]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 14], workSheet.Cells[BeginRowNumber + InD, 15]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

        }

        /// <summary>
        /// 特殊增加一空行
        /// </summary>
        /// <param name="BeginRowNumber"> 起始号码</param>
        /// <param name="InD">增加的行数</param>
        /// <param name="workSheet">工作表</param>
        public void InsertRowZsxt(int BeginRowNumber, int InD, int topInD, Microsoft.Office.Interop.Excel.Worksheet workSheet)
        {
            var range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[BeginRowNumber + InD, Type.Missing];
            range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 1], workSheet.Cells[BeginRowNumber + InD, 15]];
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            range = workSheet.Range[workSheet.Cells[topInD, 1], workSheet.Cells[BeginRowNumber + InD, 1]];
            range.Merge(Missing.Value);

            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 3], workSheet.Cells[BeginRowNumber + InD, 4]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 5], workSheet.Cells[BeginRowNumber + InD, 6]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 7], workSheet.Cells[BeginRowNumber + InD, 8]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 9], workSheet.Cells[BeginRowNumber + InD, 10]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 14], workSheet.Cells[BeginRowNumber + InD, 15]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
        }
        /// <summary>
        /// 账号增加一空行
        /// </summary>
        /// <param name="BeginRowNumber"> 起始号码</param>
        /// <param name="InD">增加的行数</param>
        /// <param name="workSheet">工作表</param>
        public void InsertAccountRow(int BeginRowNumber, int InD, Microsoft.Office.Interop.Excel.Worksheet workSheet)
        {
            var range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[BeginRowNumber + InD, Type.Missing];
            range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 1], workSheet.Cells[BeginRowNumber + InD, 3]];
            range.Merge(Missing.Value);
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 4], workSheet.Cells[BeginRowNumber + InD, 6]];
            range.Merge(Missing.Value);
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 7], workSheet.Cells[BeginRowNumber + InD, 10]];
            range.Merge(Missing.Value);

        }



        /// <summary>
        /// 运行Excel宏
        /// </summary>
        /// <param name="MacroName"></param>
        public void RunMacro(string MacroName)
        {
            myExcel.Run(MacroName, miss, miss,

                miss, miss, miss, miss, miss, miss, miss,

                miss, miss, miss, miss, miss, miss, miss,

                miss, miss, miss, miss, miss, miss, miss,

                miss, miss, miss, miss, miss, miss, miss);
        }

    }
}
